﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using DDTek.Oracle;
using Excel2DbTool.Models;
using Excel2DbTool.Helpers;
using System.Data;

namespace Excel2DbTool.Common
{
    class OracleHelper : SqlHelper
    {
        protected OracleHelper()
        {
        }

        static OracleHelper instance = null;
        public static OracleHelper Instance
        {
            get
            {
                return instance ?? (instance = new OracleHelper());
            }
        }

        public override void InsertRecord(ExcelRowRecord excelRR)
        {
            if (excelRR == null)
            {
                return;
            }

            String inserSqlStr = ((IOracleSQL)excelRR).ToInsertStr();

            Insert(inserSqlStr);
        }

        public override void BatchInsertRecord(IEnumerable<ExcelRowRecord> excelRRs)
        {
            if (excelRRs == null || excelRRs.Count() == 0)
            {
                return;
            }

            InsertToDbBySplitCollection(excelRRs);
        }

        void InsertToDbBySplitCollection(IEnumerable<Models.ExcelRowRecord> recordGroup)
        {
            var collectionsSplitted = recordGroup.SplitByPer(1500);

            try
            {
                foreach (var item in collectionsSplitted)
                {
                    string insertSqlStr = GenerateBatchInsertSqlStr(item);
                    Insert(insertSqlStr);
                }
            }
            catch (Excel2DbException ex)
            {
                throw;
            }
        }


        string GenerateBatchInsertSqlStr(IEnumerable<ExcelRowRecord> excelRRs)
        {

            StringBuilder sb = new StringBuilder(excelRRs.Count() * 100);
            sb.AppendFormat("INSERT INTO {0} ({1})", ConfigHelper.TableName, excelRRs.First().FieldNameStr);

            for (int i = 0; i < excelRRs.Count(); i++)
            {
                sb.AppendFormat(" SELECT {0} FROM dual {1}", excelRRs.ElementAt(i).FieldValStr, (i == excelRRs.Count() - 1) ? "" : "union");
            }

            string insertSqlStr = sb.ToString();

            return insertSqlStr;
        }

        public override DataTable Select(string sql)
        {
            OracleConnection conn = GetConnection();

            DataTable data = new DataTable();
            try
            {
                SelectImplement(conn, sql, data);
            }
            catch (Exception ee)
            {
                throw new Excel2DbException(ee.Message + ":" + sql, ee);
            }
            finally
            {
                conn.Close(); //关闭连接
            }

            return data;
        }

        void SelectImplement(OracleConnection conn, string sql, DataTable data)
        {
            conn.Open();
            OracleCommand cmd = conn.CreateCommand();
            cmd.CommandText = sql;

            var reader = cmd.ExecuteReader();
            var fieldCount = reader.FieldCount;

            //添加每列的列头
            for (int i = 0; i < fieldCount; i++)
            {
                DataColumn column = new DataColumn(reader.GetName(i));
                data.Columns.Add(column);
            }

            //每次循环就是一条记录
            while (reader.Read())
            {
                DataRow row = data.NewRow();
                for (int i = 0; i < fieldCount; i++)
                {
                    row[reader.GetName(i)] = reader[i];
                }
                data.Rows.Add(row);
            }
        }

        public override void Insert(String insertSqlStr)
        {
            OracleConnection conn = GetConnection();
            try
            {
                InsertImplement(conn, insertSqlStr);
            }
            catch (Exception ex)
            {
                DebugHelper.Write(insertSqlStr);
                throw new Excel2DbException(ex.Message, ex);
            }
            finally
            {
                conn.Close(); //关闭连接
            }
        }

        protected OracleConnection GetConnection()
        {
            return new OracleConnection(ConfigHelper.DbConnectStr);
        }

        void InsertImplement(OracleConnection conn, string insertSqlStr)
        {
            conn.Open();
            OracleCommand cmd = conn.CreateCommand();
            cmd.CommandText = insertSqlStr;
            cmd.ExecuteNonQuery();
        }

        public override void CreateTable()
        {
            string sql = OrginTableFieldNames.GetCreateTableSqlStr();
            ExecuteSqlReturnNoResult(sql);
        }

        public override void DumpTable()
        {
            string sql = OrginTableFieldNames.GetDropTableSqlStr();
            ExecuteSqlReturnNoResult(sql);
        }

        public override void ClearTable(string tableName)
        {
            //string sql = string.Format("TRUNCATE TABLE {0}", tableName);
            string sql = string.Format("DELETE FROM {0}", tableName);
            ExecuteSqlReturnNoResult(sql);
        }


        void ExecuteSqlReturnNoResult(string sql)
        {
            ExecuteSqlReturnAffectedRowCount(sql);
        }


        public override int GetTotalRowCountInTable(string tableName)
        {
            string sql = string.Format("SELECT COUNT(*) FROM {0}", tableName);
            return ExecuteSqlReturnAffectedRowCount(sql);
        }

        int ExecuteSqlReturnAffectedRowCount(string sql)
        {
            int affectedRowCount = 0;
            using (OracleConnection conn = GetConnection())
            {
                try
                {
                    conn.Open();
                    OracleCommand cmd = conn.CreateCommand();
                    cmd.CommandText = sql;
                    affectedRowCount = cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    DebugHelper.Write(ex.Message);
                    throw new Excel2DbException(ex.Message, ex);
                }
            }

            return affectedRowCount;
        }

        public override T GetStatisticVal<T>(string sql)
        {
            var conn = GetConnection();
            var cmd = conn.CreateCommand();
            try
            {
                cmd.CommandText = sql;
                conn.Open();
                object result = cmd.ExecuteScalar();
                
                if (result == DBNull.Value)
                {
                    return default(T);
                }

                T val = (T)Convert.ChangeType(result, typeof(T));
                return val;
            }
            catch (Exception ex)
            {
                DebugHelper.Write(sql);
                throw new Excel2DbException(ex.Message + ":" + sql, ex);
            }
            finally
            {
                conn.Close();
                cmd.Dispose();
            }
        }
    }


    [Obsolete("Do Not Use")]
    class TransactionOracleHelper : OracleHelper
    {
        private void Test_Transaction()
        {
            string myConnString = string.Empty;
            OracleConnection myConnection = new OracleConnection(myConnString);
            myConnection.Open();
            OracleCommand myCommand = new OracleCommand();
            OracleTransaction myTrans;
            // Start a local transaction 
            myTrans = myConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
            // Assign transaction object for a pending local transaction 
            myCommand.Transaction = myTrans;
            myCommand.Connection = myConnection;

            Action<string> insertAction = TransactionInsertNotCommit(myCommand);
            try
            {
                string sql = "1";
                insertAction(sql);
                sql = "2";
                insertAction(sql);
                myCommand.Transaction.Commit();
            }
            catch (Excel2DbException ex)
            {
                myCommand.Transaction.Rollback();
                throw;
            }
            finally
            {
                myCommand.Connection.Close();
                myCommand.Dispose();
            }
        }

        public Action<string> TransactionInsertNotCommit(OracleCommand commandHasSetted)
        {

            Action<string> implement = (sql) =>
            {
                try
                {
                    commandHasSetted.CommandText = sql;
                    commandHasSetted.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    //commandHasSetted.Transaction.Rollback();
                    throw new Excel2DbException(ex.Message, ex);
                }
                finally
                {
                    //commandHasSetted.Connection.Close();
                }
            };

            return implement;
        }


        void Backup()
        {
            string myConnString = string.Empty;
            OracleConnection myConnection = new OracleConnection(myConnString);
            myConnection.Open();
            OracleCommand myCommand = new OracleCommand();
            OracleTransaction myTrans;
            // Start a local transaction 
            myTrans = myConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
            // Assign transaction object for a pending local transaction 
            myCommand.Transaction = myTrans;
            myCommand.Connection = myConnection;
            try
            {
                myCommand.CommandText = "INSERT INTO Test.Dept(DeptNo, DName) Values(50, 'DEVELOPMENT')";
                myCommand.ExecuteNonQuery();
                myCommand.CommandText = "INSERT INTO Test.Dept(DeptNo, DName) Values(60, 'PRODUCTION')";
                myCommand.ExecuteNonQuery();
                myTrans.Commit();
                Console.WriteLine("Both records are written to database.");
            }
            catch (Exception e)
            {
                myTrans.Rollback();
                Console.WriteLine(e.ToString());
                Console.WriteLine("Neither record was written to database.");
            }
            finally
            {
                myConnection.Close();
            }


        }
    }
}
